# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt

import frappe
from frappe import _
from frappe.utils import flt, getdate, get_last_day, add_months, cint, nowdate
import calendar

def execute(filters=None):
    if not filters:
        filters = {}
    validate_filters(filters)
    
    # 获取财政年度信息
    fy = frappe.get_doc("Fiscal Year", filters.fiscal_year)
    filters["fy_start_date"] = getdate(fy.year_start_date)
    filters["fy_end_date"] = getdate(fy.year_end_date)
    
    # 计算YTD截止日期（选中月份的最后一天）
    month_num = list(calendar.month_name).index(filters.month)
    fy_start_year = filters["fy_start_date"].year
    ytd_month_date = getdate(f"{fy_start_year}-{month_num:02d}-01")
    if ytd_month_date < filters["fy_start_date"]:
        ytd_month_date = add_months(ytd_month_date, 12)
    filters["ytd_end_date"] = get_last_day(ytd_month_date)
    if filters["ytd_end_date"] > filters["fy_end_date"]:
        filters["ytd_end_date"] = filters["fy_end_date"]
    
    columns = get_columns(filters)
    data = get_data(filters)  # 获取账户/成本中心数据
    
    # 向上汇总到父级
    data = accumulate_values_into_parents(data, filters)
    
    # 按层级排序
    budget_against = filters.get("budget_against", "Account")
    entity_key = "account" if budget_against == "Account" else "cost_center"
    doctype = "Account" if budget_against == "Account" else "Cost Center"
    data = sorted(data, key=lambda x: frappe.get_cached_value(doctype, x[entity_key], "lft"))
    
    # 过滤零值行
    if not cint(filters.get("show_zero_values", 0)):
        data = [row for row in data if not is_zero_row(row)]
    
    return columns, data

def validate_filters(filters):
    if not filters.get("fiscal_year"):
        frappe.throw(_("Please select Fiscal Year"))
    if not filters.get("month"):
        frappe.throw(_("Please select Month"))
    if filters.get("month") not in list(calendar.month_name):
        frappe.throw(_("Invalid Month. Please select a valid month name."))

def get_columns(filters):
    budget_against = filters.get("budget_against", "Account")
    # 动态第一列（账户/成本中心）
    first_column = {
        "fieldname": "account" if budget_against == "Account" else "cost_center",
        "label": _("Account") if budget_against == "Account" else _("Cost Center"),
        "fieldtype": "Link",
        "options": "Account" if budget_against == "Account" else "Cost Center",
        "width": 300
    }
    
    columns = [first_column] + [
        {
            "fieldname": "budget",
            "label": _("Budget (FY Total)"),
            "fieldtype": "Currency",
            "options": "currency",
            "width": 120
        },
        {
            "fieldname": "budget_ytd",
            "label": _("Budget YTD"),
            "fieldtype": "Currency",
            "options": "currency",
            "width": 120
        },
        {
            "fieldname": "budget_boy",
            "label": _("Budget BOY"),
            "fieldtype": "Currency",
            "options": "currency",
            "width": 120
        },
        {
            "fieldname": "actual",
            "label": _("Actual (FY Total)"),
            "fieldtype": "Currency",
            "options": "currency",
            "width": 120
        },
        {
            "fieldname": "actual_ytd",
            "label": _("Actual YTD"),
            "fieldtype": "Currency",
            "options": "currency",
            "width": 120
        },
        {
            "fieldname": "actual_boy",
            "label": _("Actual BOY"),
            "fieldtype": "Currency",
            "options": "currency",
            "width": 120
        },
        {
            "fieldname": "variance",
            "label": _("Variance"),
            "fieldtype": "Currency",
            "options": "currency",
            "width": 120
        },
        {
            "fieldname": "variance_percent",
            "label": _("Variance %"),
            "fieldtype": "Percent",
            "width": 120
        }
    ]
    return columns

def get_filters(filters):
    """定义过滤器（在报表配置中使用）"""
    return [
        {
            "fieldname": "company",
            "label": _("Company"),
            "fieldtype": "Link",
            "options": "Company",
            "reqd": 1,
            "default": frappe.defaults.get_user_default("Company")
        },
        {
            "fieldname": "from_date",
            "label": _("From Date"),
            "fieldtype": "Date",
            "reqd": 1
        },
        {
            "fieldname": "to_date",
            "label": _("To Date"),
            "fieldtype": "Date",
            "reqd": 1
        },
        {
            "fieldname": "report_type",
            "label": _("Report Type"),
            "fieldtype": "Select",
            "options": "\nBalance Sheet\nProfit and Loss",
            "default": "Profit and Loss"
        },
        {
            "fieldname": "root_type",
            "label": _("Root Type"),
            "fieldtype": "Select",
            "options": "\nAsset\nLiability\nEquity\nIncome\nExpense"
        },
        {
            "fieldname": "show_zero_values",
            "label": _("Show Zero Values"),
            "fieldtype": "Check",
            "default": 0
        }
    ]

def get_accounts(filters):
    """获取所有账户（包括父账户）"""
    filters = {
        "company": filters.company,
        "root_type": filters.root_type,
        "report_type": filters.report_type
    }
    return frappe.db.sql("""
        select name, account_name, parent_account, is_group, root_type, report_type
        from `tabAccount`
        where company = %(company)s
        {root_type_filter}
        {report_type_filter}
        order by lft
    """.format(
        root_type_filter="and root_type = %(root_type)s" if filters["root_type"] else "",
        report_type_filter="and report_type = %(report_type)s" if filters["report_type"] else ""
    ), filters, as_dict=True)

def get_cost_centers(filters):
    """获取所有成本中心（包括父级）"""
    filters = {
        "company": filters.company
    }
    return frappe.db.sql("""
        select name, cost_center_name, parent_cost_center, is_group
        from `tabCost Center`
        where company = %(company)s
        order by lft
    """, filters, as_dict=True)

def get_data(filters):
    """获取账户/成本中心数据，末级实体计算实际/预算值"""
    data = []
    budget_against = filters.get("budget_against", "Account")
    
    # 选择实体类型（账户/成本中心）
    if budget_against == "Account":
        entities = get_accounts(filters)
        entity_key = "account"
        parent_key = "parent_account"
    else:
        entities = get_cost_centers(filters)
        entity_key = "cost_center"
        parent_key = "parent_cost_center"
    
    for entity in entities:
        row = {
            entity_key: entity["name"],
            parent_key: entity.get(parent_key),
            "is_group": entity["is_group"],
            "budget": 0.0,
            "budget_ytd": 0.0,
            "budget_boy": 0.0,
            "actual": 0.0,
            "actual_ytd": 0.0,
            "actual_boy": 0.0,
            "variance": 0.0,
            "variance_percent": 0.0,
            "currency": frappe.get_cached_value('Company', filters.company, 'default_currency')
        }
        
        # 仅末级实体计算值
        if not entity["is_group"]:
            # 预算数据
            budget_fy = get_budget_amount(entity["name"], filters["fy_start_date"], filters["fy_end_date"], filters)
            budget_ytd = get_budget_amount(entity["name"], filters["fy_start_date"], filters["ytd_end_date"], filters)
            budget_boy = budget_fy - budget_ytd
            
            # 实际数据
            actual_fy = get_actual_amount(entity["name"], filters["fy_start_date"], filters["fy_end_date"], filters)
            actual_ytd = get_actual_amount(entity["name"], filters["fy_start_date"], filters["ytd_end_date"], filters)
            actual_boy = actual_fy - actual_ytd
            
            # 差异计算
            variance = actual_fy - budget_fy
            variance_percent = (variance / budget_fy) * 100 if budget_fy else 0
            
            row.update({
                "budget": budget_fy,
                "budget_ytd": budget_ytd,
                "budget_boy": budget_boy,
                "actual": actual_fy,
                "actual_ytd": actual_ytd,
                "actual_boy": actual_boy,
                "variance": variance,
                "variance_percent": variance_percent
            })
        
        data.append(row)
    return data

def accumulate_values_into_parents(data, filters):
    """向上汇总子实体数据到父实体"""
    budget_against = filters.get("budget_against", "Account")
    entity_key = "account" if budget_against == "Account" else "cost_center"
    parent_key = "parent_account" if budget_against == "Account" else "parent_cost_center"
    doctype = "Account" if budget_against == "Account" else "Cost Center"
    
    # 构建实体映射和父子关系
    entity_map = {row[entity_key]: row for row in data}
    parent_children_map = {}
    
    for row in data:
        parent = row[parent_key]
        if parent and parent in entity_map:
            parent_children_map.setdefault(parent, []).append(row[entity_key])
    
    # 按层级从下往上汇总
    sorted_entities = sorted(data, key=lambda x: frappe.get_cached_value(doctype, x[entity_key], "lft"), reverse=True)
    
    for row in sorted_entities:
        entity = row[entity_key]
        parent = row[parent_key]
        
        if parent and parent in entity_map:
            parent_row = entity_map[parent]
            # 汇总金额字段
            for field in ["budget", "budget_ytd", "budget_boy", 
                          "actual", "actual_ytd", "actual_boy", 
                          "variance"]:
                parent_row[field] = flt(parent_row[field]) + flt(row[field])
            
            # 重新计算差异百分比
            if parent_row["budget"] != 0:
                parent_row["variance_percent"] = (parent_row["variance"] / parent_row["budget"]) * 100
    
    return list(entity_map.values())

def is_zero_row(row):
    """判断是否为零值行"""
    amount_fields = ["budget", "budget_ytd", "budget_boy", 
                     "actual", "actual_ytd", "actual_boy", 
                     "variance"]
    return all(flt(row[field]) == 0 for field in amount_fields)

def get_budget_amount(entity, start_date, end_date, filters):
    """获取预算金额（适配Account/Cost Center）"""
    budget_against = filters.get("budget_against", "Account")
    docstatus = {"Draft": 0, "Submitted": 1, "Cancelled": 2}

    conditions = {
        "company": filters.company,
        "entity": entity,
        "start_date": start_date,
        "end_date": end_date,
        "docstatus": docstatus["Submitted"],
        "fiscal_year": filters.fiscal_year
    }

    # 预算主表条件
    budget_conditions = """
        b.company = %(company)s
        and b.docstatus = %(docstatus)s
        and b.fiscal_year = %(fiscal_year)s
    """

    # 动态关联子表和字段
    budget_child_table = f"`tabBudget Account`"
    budget_against_field = budget_against.lower().replace(" ", "_")
    detail_conditions = f"bd.{budget_against_field} = %(entity)s"

    # 额外成本中心筛选（仅Account模式）
    if filters.get("cost_center") and budget_against == "Account":
        detail_conditions += " and bd.cost_center = %(cost_center)s"
        conditions["cost_center"] = filters.cost_center

    # 月度分配条件
    month_conditions = """
        mp.parent = bd.monthly_distribution
        and mp.month in (
            select monthname(posting_date) 
            from `tabGL Entry` 
            where posting_date between %(start_date)s and %(end_date)s
            group by monthname(posting_date)
        )
    """

    query = f"""
        select sum(bd.budget_amount * mp.percentage_allocation / 100) as budget_amount
        from `tabBudget` b
        inner join {budget_child_table} bd on b.name = bd.parent
        inner join `tabMonthly Distribution Percentage` mp on {month_conditions}
        where {budget_conditions}
        and {detail_conditions}
    """

    result = frappe.db.sql(query, conditions, as_dict=True)
    return flt(result[0]["budget_amount"]) if result else 0

def get_actual_amount(entity, start_date, end_date, filters):
    """获取实际发生额（适配Account/Cost Center）"""
    budget_against = filters.get("budget_against", "Account")
    conditions = {
        "company": filters.company,
        "entity": entity,
        "start_date": start_date,
        "end_date": end_date
    }

    # 基础条件
    gl_conditions = "gle.company = %(company)s"
    gl_conditions += " and gle.posting_date between %(start_date)s and %(end_date)s"

    # 动态筛选字段（账户/成本中心）
    if budget_against == "Account":
        gl_conditions += " and gle.account = %(entity)s"
    else:
        gl_conditions += " and gle.cost_center = %(entity)s"

    # 额外筛选（仅Account模式下的成本中心）
    if filters.get("cost_center") and budget_against == "Account":
        gl_conditions += " and gle.cost_center = %(cost_center)s"
        conditions["cost_center"] = filters.cost_center

    query = f"""
        select sum(gle.credit) - sum(gle.debit) as actual_amount
        from `tabGL Entry` gle
        where {gl_conditions}
    """

    result = frappe.db.sql(query, conditions, as_dict=True)
    return flt(result[0]["actual_amount"]) if result else 0